import traceback
import pyautogui
import os
import time
import pyperclip as clip
import json
import pandas as pd
from datetime import datetime
from sqlalchemy import create_engine
import pypinyin
import random
from jsjj.util.configUtil import *
from jsjj.util.dbUtil import *
from jsjj.util.emailUtil import *
from jsjj.util.timeUtil import *
from jsjj.util.logUtil import *

# 运行模式: 日模式；周模式；月模式
runMode = '周模式'
modeName = '拼多多_电霸_行业总览'


# 左边点击，直到点击到一级类目，该过程不参与循环
def UntilCat1():
    # 关闭电霸每天晚上会弹出的一个屏蔽页面
    pyautogui.FAILSAFE = False
    sleep(2)

    # 关闭广告页面
    pyautogui.moveTo(1413, 385)
    pyautogui.click()

    sleep(4)
    # 向上翻到顶叶
    pyautogui.scroll(5000)
    sleep(6)

    # 顶部行业菜单
    pyautogui.moveTo(313, 190, 2)
    pyautogui.click()

    # 拼多多-电霸-左侧菜单-行业总览
    pyautogui.moveTo(120, 435, 2)
    pyautogui.click()

    # if runMode == '日模式':
    #     # 点击昨日
    #     pyautogui.moveTo(1587, 364, 2)
    #     pyautogui.click()
    #
    # elif runMode == '月模式':
    #     # 点击30天（缺点是必须每月1日运行）（后续再解决这个缺陷）
    #     pyautogui.moveTo(1720, 363, 2)
    #     pyautogui.click()
    #
    # else:
    #     raise Exception("暂不支持周模式")

    # 拼多多-电霸-中间左上角主营类目
    sleep(8)
    pyautogui.moveTo(352, 250)
    pyautogui.click()

    # 焦点切换到输入框
    sleep(2)
    pyautogui.keyDown('tab')
    pyautogui.keyUp('tab')

    sleep(2)
    # pyautogui.typewrite(pinyin('家纺家具家装') + "1")
    clip.copy('家纺家具家装')  # 先复制
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴

    pyautogui.moveTo(352, 280, 2)
    pyautogui.click()

    pyautogui.moveTo(333, 327, 4)
    pyautogui.click()

    # 拼多多-电霸-中间-一级类目后台下拉菜单
    sleep(6)
    pyautogui.moveTo(524, 296)
    pyautogui.click()

    sleep(2)
    # pyautogui.typewrite(pinyin('住宅家具') + "1")
    clip.copy('住宅家具')  # 先复制
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴

    sleep(2)
    pyautogui.keyDown('enter')
    pyautogui.keyUp('enter')


def LoopCat2(cat1MenuName, cat2MenuName, cat1, cat2, conn):
    # 一级类目也是需要的
    sleep(2)
    pyautogui.moveTo(486, 302)
    pyautogui.click()

    # 成套家具
    sleep(2)
    # pyautogui.typewrite(cat2MenuName)
    clip.copy(cat1MenuName)  # 先复制
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴

    sleep(2)
    pyautogui.keyDown('enter')
    pyautogui.keyUp('enter')

    # 拼多多-电霸-中间-二级类目后台下拉菜单
    sleep(2)
    pyautogui.moveTo(904, 296)
    pyautogui.click()

    # 成套家具
    sleep(2)
    # pyautogui.typewrite(cat2MenuName)
    clip.copy(cat2MenuName)  # 先复制
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴

    sleep(2)
    pyautogui.keyDown('enter')
    pyautogui.keyUp('enter')

    # # 搜索
    # sleep(4)
    # pyautogui.moveTo(1119, 302)
    # pyautogui.click()

    sleep(4)
    pyautogui.keyDown('f12')
    pyautogui.keyUp('f12')

    # F12之后，再按一次搜索图标按钮
    sleep(4)
    pyautogui.moveTo(974, 333)
    pyautogui.click()
    sleep(6)

    # 搜索后立刻清除network，否则选json文件会选错
    sleep(4)
    pyautogui.moveTo(1540, 142)
    pyautogui.click()

    # F12之后
    if runMode == '日模式':
        # 点击昨日
        pyautogui.moveTo(1170, 364, 4)
        pyautogui.click()
        #wrong

    elif runMode == '月模式':
        # 点击30天（缺点是必须每月1日运行）（后续再解决这个缺陷）
        pyautogui.moveTo(1307, 364, 4)
        pyautogui.click()
        #wrong
    elif runMode == '周模式':

        pyautogui.moveTo(1452, 382, 4)
        pyautogui.scroll(-2200)
        pyautogui.moveTo(1239, 328, 4)
        pyautogui.click()
        sleep(4)
        pyautogui.moveTo(1452, 382, 1)
        pyautogui.scroll(2400)

    # F12 filterbox
    sleep(8)
    pyautogui.moveTo(1587, 166)  # 1347  -> 1587   240
    pyautogui.doubleClick()

    # 英文输入
    sleep(2)
    # pyautogui.typewrite("getchildsacontrast")
    clip.copy("getchildsacontrast")  # 先复制
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴

    pyautogui.keyDown('enter')
    pyautogui.keyUp('enter')

    # 拼多多-电霸-F12后的Name区域选择
    sleep(4)
    pyautogui.moveTo(1538, 320)
    pyautogui.click()

    # 拼多多-电霸-F12后的Response区域选择
    sleep(2)
    pyautogui.moveTo(1838, 490)
    pyautogui.click()

    sleep(2)
    pyautogui.hotkey('ctrl', 'a')

    sleep(2)
    pyautogui.hotkey('ctrl', 'c')

    # 读取剪切板内容
    sleep(2)
    value = clip.paste()
    paste2Df2Mysql(value, cat1, cat2, cat2MenuName, engine, conn)


def paste2Df2Mysql(value, cat1, cat2, cat2MenuName, engine, conn):
    jsonFromWeb = json.loads(value)
    # 对json的格式进行转化
    # jsonFromWeb = '{"seriesType":"bar","yAxisData":["\u9910\u5385\u6210\u5957\u5bb6\u5177","\u529f\u80fd\u623f\u6210\u5957\u5bb6\u5177","\u5ba2\u5385\u6210\u5957\u5bb6\u5177","\u95e8\u5385\u6210\u5957\u5bb6\u5177","\u5176\u4ed6\u6210\u5957\u5bb6\u5177","\u4e66\u623f\u6210\u5957\u5bb6\u5177","\u5367\u5ba4\u6210\u5957\u5bb6\u5177"],"seriesBarGap":0,"ifDataZoom":1,"series":[{"name":"\u9500\u91cf","data":[318,0,1475,2,0,0,23]},{"name":"\u9500\u552e\u989d","data":[99730.25,0,912283.76,1196,0,0,13626.9]}]}'
    jsonDf = {'catLeaf': jsonFromWeb['yAxisData'],
              'salesVolume': jsonFromWeb['series'][0]['data'],
              'salesAmount': jsonFromWeb['series'][1]['data']
              }
    df = pd.read_json(json.dumps(jsonDf))
    df['statDate'] = datetime.datetime.now()
    # 住宅家具
    df['cat1'] = cat1
    # 成套家具
    df['cat2'] = cat2

    df['运行模式'] = runMode
    df['统计月'] = ''
    df['统计周'] = ''
    df['统计日'] = ''
    df['统计日周月'] = ''
    if runMode == '月模式':
        df['统计月'] = str(datetime.datetime.now().month - 1) + "月"
        df['统计日周月'] = df['统计月']
    if runMode == '周模式':
        df['统计周'] = getLastWeekOfYear(datetime.datetime.now().year, datetime.datetime.now().month,datetime.datetime.now().day)
        df['统计日周月'] = df['统计周']
    if runMode == '日模式':
        df['统计日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d %H:%M:%S")  # 减一天，统计的是昨天的数据
        df['统计日周月'] = df['统计日']
    df['插入时间'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

    # 解决可能出现的超时问题bugfix 2020.08.18
    conn.connection.connection.ping(reconnect=True)
    df.to_sql(name='拼多多_电霸_行业总览', con=conn, if_exists='append', index=False)
    print("执行了一次保存:" + cat2MenuName + ',录入了:' + str(len(jsonFromWeb['series'][0]['data'])) + '条数据')

    sleep(4)
    pyautogui.keyDown('f12')
    pyautogui.keyUp('f12')
    sleep(4)


def pinyin(word):
    s = ''
    for i in pypinyin.pinyin(word, style=pypinyin.NORMAL):
        s += ''.join(i)
    return s


def executeCatchWeb(engine, conn):
    try:
        LogTaskAndMachine('拼多多_电霸_行业总览', engine, conn, '', runMode)
        UntilCat1()

        df = pd.read_sql(
            sql="select distinct cat1,cat2 from 平台_类目表 t where channel='拼多多' and dianba_hyzl='0' and t.排除 is null order by priority asc",
            con=engine)
        # 行业总览只到了二级目录
        # dfCat = df[(df['channel'] == '拼多多') & (df['dianba_hyzl'] == '0')][
        #     ['cat1', 'cat2']].drop_duplicates()
        for index, row in df.iterrows():
            LoopCat2(row['cat1'], row['cat2'], row['cat1'], row['cat2'], conn)

            # 完成一个更新一个
            engine.execute(" update 平台_类目表 set dianba_hyzl='1' where cat1='" + row['cat1'] + "' and cat2='" + row[
                'cat2'] + "' and channel='拼多多' ")

            # 避免客户端IE操作过于频繁
            # sleep(random.randint(1, 2) * 60)
            sleep(100)   # 40->100
    except Exception as e:
        traceback.print_exc()
        sendAlert('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1], engine, conn)
        print('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1])
        return
    sendFinalSuccessEmail('●正常完成●' + modeName, '', engine, conn, modeName, runMode)


if __name__ == '__main__':
    # 本函数调试用
    # 连接database
    engine, conn = getConn()
    executeCatchWeb(engine, conn)
